package com.apes.autoparts.product.repository;

import com.apes.autoparts.product.model.*;
import com.apes.framework.jpa.Connector;
import com.apes.framework.jpa.generator.service.SequenceService;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.MD5Util;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.Set;

/**
 * @className: com.apes.autoparts.product.repository.ProductDao
 * @description: 描述 标准商品数据批量插入
 * @author: wsl
 * @create: 2022-10-29 15:08
 */

@Component("productDao")
public class ProductDao extends ApesDao {

    private String unique;

    @Autowired
    private Connector connector;

    @Autowired
    private SequenceService sequenceService;

    @Autowired
    private  AccumulatorProductRepository accumulatorProductRepository;

    @Autowired
    private  BrandProductRepository brandProductRepository;

    @Autowired
    private  TyreProductRepository tyreProductRepository;

    @Autowired
    private OriginalProductRepository originalProductRepository;

    @Autowired
    private EPCVersionContrastRepository epcVersionContrastRepository;

    @Autowired
    private OilyProductRepository oilyProductRepository;

    /**
     * 批量插入品牌件全车标准商品
     *
     * @param brandProducts
     */
    public void brandBatchInsert(List<BrandProduct> brandProducts) {
        SimpleDateFormat sdf = new SimpleDateFormat("yy-mm-dd HH:mm:ss");
        Date date = new Date();
        String sql = "insert into auto_brand_product(id, brand, brand_mark, name,create_date,ERP_product_id) values (?,?,?,?,?,?)";
        Connection conn = connector.getConnection();
        PreparedStatement pst = null;
        Set<String> set = brandProductRepository.findAllId();
        try {
            String id = sequenceService.getId(BrandProduct.CONST_TABLE_NAME);
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < brandProducts.size(); i++) {
                BrandProduct brandProduct = brandProducts.get(i);

                do {
                    id = MD5Util.encrypt(Math.random() + brandProduct.getBrandMark() + brandProduct.getBrand() + id).substring(0, 6);
                }while (!set.add(id));
                brandProduct.setId(id);
                pst.setString(1, id);
                pst.setString(2, nullToEmpty(brandProduct.getBrand()));
                pst.setString(3, nullToEmpty(brandProduct.getBrandMark()));
                pst.setString(4, nullToEmpty(brandProduct.getName()));
                pst.setDate(5, new java.sql.Date(date.getTime()));
                pst.setString(6, nullToEmpty(brandProduct.getERPProductId()));
                //添加到批处理对象
                pst.addBatch();
                //每添加500条执行一次
                if ((i + 1) % 500 == 0) pst.executeBatch();
            }
            pst.executeBatch();
        } catch (SQLException throwables) {
            if (throwables.toString().contains("ORA-00001"))
                throw new RuntimeException("导入失败，数据已存在！");
            if (throwables.toString().contains("ORA-01400"))
                throw new RuntimeException("导入失败，必填字段不能为空！");
            throw new RuntimeException(throwables);
        } finally {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 批量插入轮胎标准商品
     *
     * @param tyreProducts
     */
    public void tyreProductInsert(List<TyreProduct> tyreProducts) {
        String sql = "insert into auto_tyre_product(id,brand, pn, specification, figure,load_index,speed_class,anti_explosion,describe,unit,bar_code,create_date, ERP_product_id) " +
                "values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = connector.getConnection();
        Set<String> set = tyreProductRepository.findAllId();
        Date date = new Date();
        PreparedStatement pst = null;
        try {
            String id = "";
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < tyreProducts.size(); i++) {
                TyreProduct tyreProduct = tyreProducts.get(i);
                do{
                id = MD5Util.encrypt(Math.random() +tyreProduct.getBarCode()+tyreProduct.getFigure() + tyreProduct.getLoadIndex() + id).substring(0, 8);
                 }while (!set.add(id));
                tyreProduct.setId(id);
                pst.setString(1, id);
                pst.setString(2, nullToEmpty(tyreProduct.getBrand()));
                pst.setString(3, nullToEmpty(tyreProduct.getPn()));
                pst.setString(4, nullToEmpty(tyreProduct.getSpecification()));
                pst.setString(5, nullToEmpty(tyreProduct.getFigure()));
                pst.setString(6, tyreProduct.getLoadIndex());
                pst.setString(7, nullToEmpty(tyreProduct.getSpeedClass()));
                pst.setBoolean(8, tyreProduct.isAntiExplosion());
                pst.setString(9, nullToEmpty(tyreProduct.getDescribe()));
                pst.setString(10, nullToEmpty(tyreProduct.getUnit()));
                pst.setString(11, nullToEmpty(tyreProduct.getBarCode()));
                pst.setDate(12, new java.sql.Date(date.getTime()));
                pst.setString(13, nullToEmpty(tyreProduct.getERPProductId()));
                //添加到批处理对象
                pst.addBatch();
                //每添加500条执行一次
                if ((i + 1) % 500 == 0) pst.executeBatch();
            }
            pst.executeBatch();
        } catch (SQLException throwables) {
            if (throwables.toString().contains("ORA-00001"))
                throw new RuntimeException("导入失败，数据已存在！");
            if (throwables.toString().contains("ORA-01400"))
                throw new RuntimeException("导入失败，必填字段不能为空！");
            throw new RuntimeException(throwables);
        } finally {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 批量插入蓄电池标准商品
     *
     * @param accumulatorProducts
     */
    public void accumulatorProductInsert(List<AccumulatorProduct> accumulatorProducts) {
        String sql = "insert into auto_accumulator_product(id,brand, pn, unit, grade,reference,type,series,capacity,voltage,sizes,terminal,electricity,describe,bar_code,create_date,ERP_product_id) " +
                "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = connector.getConnection();
        Date date = new Date();
        PreparedStatement pst = null;
        Set<String> set = accumulatorProductRepository.findAllId();
        try {
            String id = "";
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < accumulatorProducts.size(); i++) {
                AccumulatorProduct accumulatorProduct = accumulatorProducts.get(i);

                do {
                    id = MD5Util.encrypt(Math.random() + accumulatorProduct.getPn() + accumulatorProduct.getBrand() + accumulatorProduct.getGrade() + id).substring(0, 8);
                }while(!set.add(id));
                accumulatorProduct.setId(id);
                pst.setString(1, id);
                pst.setString(2, nullToEmpty(accumulatorProduct.getBrand()));
                pst.setString(3, nullToEmpty(accumulatorProduct.getPn()));
                pst.setString(4, nullToEmpty(accumulatorProduct.getUnit()));
                pst.setString(5, nullToEmpty(accumulatorProduct.getGrade()));
                pst.setString(6, nullToEmpty(accumulatorProduct.getReference()));
                pst.setString(7, nullToEmpty(accumulatorProduct.getType()));
                pst.setString(8,nullToEmpty( accumulatorProduct.getSeries()));
                pst.setDouble(9, accumulatorProduct.getCapacity());
                pst.setDouble(10, accumulatorProduct.getVoltage());
                pst.setString(11, nullToEmpty(accumulatorProduct.getSizes()));
                pst.setString(12, nullToEmpty(accumulatorProduct.getTerminal()));
                pst.setString(13, nullToEmpty(accumulatorProduct.getElectricity()));
                pst.setString(14, nullToEmpty(accumulatorProduct.getDescribe()));
                pst.setString(15, nullToEmpty(accumulatorProduct.getBarCode()));
                pst.setDate(16, new java.sql.Date(date.getTime()));
                pst.setString(17, nullToEmpty(accumulatorProduct.getERPProductId()));
                //添加到批处理对象
                pst.addBatch();
                //每添加500条执行一次
                if ((i + 1) % 500 == 0) pst.executeBatch();
            }
            pst.executeBatch();
        } catch (SQLException throwables) {
            if (throwables.toString().contains("ORA-00001"))
                throw new RuntimeException("导入失败，数据已存在！");
            if (throwables.toString().contains("ORA-01400"))
                throw new RuntimeException("导入失败，必填字段不能为空！");
            throw new RuntimeException(throwables);
        } finally {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 批量插入机油类标准商品
     *
     * @param oilyProducts
     */
    public void oilyBatchInsert(List<OilyProduct> oilyProducts) {
        String sql = "insert into auto_oily_product(id, brand, name, unit, catena, model_number, capacity, api_class," +
                "viscosity_class, type, engine_oil_type, description, bar_code,create_date,ERP_product_id) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = connector.getConnection();
        Date date = new Date();
        Set<String> set = oilyProductRepository.findAllId();
        PreparedStatement pst = null;
        try {
            String id = "";
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < oilyProducts.size(); i++) {
                OilyProduct oilyProduct = oilyProducts.get(i);
                do{
                id = MD5Util.encrypt(oilyProduct.getBrand() + Math.random() + oilyProduct.getName() + id).substring(0, 6);
            }while(!set.add(id));
                oilyProduct.setId(id);
                pst.setString(1, id);
                pst.setString(2, nullToEmpty(oilyProduct.getBrand()));
                pst.setString(3, nullToEmpty(oilyProduct.getName()));
                pst.setString(4, nullToEmpty(oilyProduct.getUnit()));
                pst.setString(5, nullToEmpty(oilyProduct.getCatena()));
                pst.setString(6, nullToEmpty(oilyProduct.getModelNumber()));
                pst.setDouble(7, oilyProduct.getCapacity() == null ? 0L : oilyProduct.getCapacity());
                pst.setString(8, nullToEmpty(oilyProduct.getApiClass()));
                pst.setString(9, nullToEmpty(oilyProduct.getViscosityClass()));
                pst.setString(10, nullToEmpty(oilyProduct.getType()));
                pst.setString(11, nullToEmpty(oilyProduct.getEngineOilType()));
                pst.setString(12, nullToEmpty(oilyProduct.getDescription()));
                pst.setString(13, nullToEmpty(oilyProduct.getBarCode()));
                pst.setDate(14, new java.sql.Date(date.getTime()));
                pst.setString(15, nullToEmpty(oilyProduct.getERPProductId()));
                //添加到批处理对象
                pst.addBatch();
                //每添加500条执行一次
                if ((i + 1) % 500 == 0) pst.executeBatch();
            }
            pst.executeBatch();
        } catch (SQLException throwables) {
            if (throwables.toString().contains("ORA-00001"))
                throw new RuntimeException("导入失败，数据已存在！");
            if (throwables.toString().contains("ORA-01400"))
                throw new RuntimeException("导入失败，必填字段不能为空！");
            throw new RuntimeException(throwables);
        } finally {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 批量插入EPC版本对照
     *
     * @param epcVersionContrasts
     */
    public void epcVersionInsert(List<EPCVersionContrast> epcVersionContrasts) {
        String sql = "insert into EPC_version_contrast(id, epc_version, auto_label, auto_brand,create_date) " + "values (?,?,?,?,?)";
        Connection conn = connector.getConnection();
        Date date = new Date();
        Set<String> set = epcVersionContrastRepository.findAllId();
        PreparedStatement pst = null;
        try {
            String id = sequenceService.getId(EPCVersionContrast.CONST_TABLE_NAME);
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < epcVersionContrasts.size(); i++) {
                EPCVersionContrast epcVersionContrast = epcVersionContrasts.get(i);
                do{
                    id = MD5Util.encrypt(Math.random() + epcVersionContrast.getAutoBrand() + epcVersionContrast.getEpcVersion() + epcVersionContrast.getAutoLabel() + id).substring(0, 8);
                }while(!set.add(id));
                epcVersionContrast.setId(id);
                pst.setString(1, id);
                pst.setString(2, nullToEmpty(epcVersionContrast.getEpcVersion()));
                pst.setString(3, nullToEmpty(epcVersionContrast.getAutoLabel()));
                pst.setString(4, nullToEmpty(epcVersionContrast.getAutoBrand()));
                pst.setDate(5, new java.sql.Date(date.getTime()));
                //添加到批处理对象
                pst.addBatch();
                //每添加500条执行一次
                if ((i + 1) % 500 == 0) pst.executeBatch();
            }
            pst.executeBatch();
        } catch (SQLException throwables) {
            if (throwables.toString().contains("ORA-00001"))
                throw new RuntimeException("导入失败，请重新导入！");
            if (throwables.toString().contains("ORA-01400"))
                throw new RuntimeException("导入失败，必填字段不能为空！");
            throw new RuntimeException(throwables);
        } finally {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 原厂标准商品
     *
     * @param originalProducts
     */
    public void originalInsert(List<OriginalProduct> originalProducts) {
        String sql = "insert into auto_original_product(id, epc_version, oe_mark,name,create_date ) " + "values (?,?,?,?,?)";
        Connection conn = connector.getConnection();
        Set<String> set = originalProductRepository.findAllId();
        Date date = new Date();
        PreparedStatement pst = null;
        try {
            String id = sequenceService.getId(OriginalProduct.CONST_TABLE_NAME);
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < originalProducts.size(); i++) {
                OriginalProduct originalProduct = originalProducts.get(i);
                do {
                    id = MD5Util.encrypt(Math.random() + originalProduct.getName() + originalProduct.getOeMark() + originalProduct.getEpcVersion() + id).substring(0, 8);
                }while (!set.add(id));
                originalProduct.setId(id);
                pst.setString(1, id);
                pst.setString(2, nullToEmpty(originalProduct.getEpcVersion()));
                pst.setString(3, nullToEmpty(originalProduct.getOeMark()));
                pst.setString(4, nullToEmpty(originalProduct.getName()));
                pst.setDate(5, new java.sql.Date(date.getTime()));
                //添加到批处理对象
                pst.addBatch();
                //每添加500条执行一次
                if ((i + 1) % 500 == 0) pst.executeBatch();
            }
            pst.executeBatch();
        } catch (SQLException throwables) {
            if (throwables.toString().contains("ORA-00001"))
                throw new RuntimeException("导入失败，原件件号加EPC版本号不能重复！");
            if (throwables.toString().contains("ORA-01400"))
                throw new RuntimeException("导入失败，必填字段不能为空！");
            throw new RuntimeException(throwables);
        } finally {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    private String nullToEmpty(String object) {
        return Objects.isNull(object) ? "" : object;
    }
}
